﻿Imports NVelocity
Imports NVelocity.App
Imports NVelocity.Context

Imports System.Collections.Generic
Imports System.IO
Imports NPOI.HSSF.UserModel
Imports System.Data

Partial Class WebForm_SearchVBook
    Inherits MyUserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
        
            RaiseEvent Loaded(Me, New EventArgs)

        End If
        Me.phUpload.Visible = Me.IsVisible
        '志工隊不能匯出請領清冊
        If Me.CanEdit = False Then
            Me.btnExcel.Visible = False
            hlinkDoc.Visible = False
        Else
            Me.btnExcel.Visible = True
            hlinkDoc.Visible = True
        End If
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnExcel)
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnUpload)
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub

    Protected Sub gv_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gv.SelectedIndexChanged
        RaiseEvent SelectedIndexChanged(Me, New EventArgs)
    End Sub

    Protected Sub btnQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnQuery.Click
        Me.BindGV()
    End Sub

    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        Me.Export()
    End Sub

    Protected Sub gv_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gv.Sorting
        Me.BindGV()
    End Sub

    '上傳志工Excel
    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click

        If Me.FileUpload1.HasFile Then

            Try

                Dim workbook As New HSSFWorkbook(Me.FileUpload1.FileContent)
                Dim sheet As HSSFSheet = DirectCast(workbook.GetSheetAt(0), HSSFSheet)

                Dim table As New DataTable()
                table.TableName = "table"

                Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(0), HSSFRow)
                Dim cellCount As Integer = headerRow.LastCellNum

                table.Columns.Add("NO", Type.GetType("System.String"))
                table.Columns.Add("CountyID", Type.GetType("System.String"))
                table.Columns.Add("SerialGroup", Type.GetType("System.String"))
                table.Columns.Add("ID", Type.GetType("System.String"))
                table.Columns.Add("Name", Type.GetType("System.String"))
                table.Columns.Add("SerialNumber", Type.GetType("System.String"))
                table.Columns.Add("StatePaperID", Type.GetType("System.String"))
                table.Columns.Add("date", Type.GetType("System.String"))

                Dim rowCount As Integer = sheet.LastRowNum

                For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum
                    Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
                    If row Is Nothing OrElse row.GetCell(0) Is Nothing OrElse row.GetCell(1) Is Nothing OrElse row.GetCell(2) Is Nothing OrElse row.GetCell(3) Is Nothing OrElse row.GetCell(4) Is Nothing OrElse row.GetCell(5) Is Nothing OrElse row.GetCell(6) Is Nothing OrElse row.GetCell(7) Is Nothing Then
                        Exit For
                    End If
                    Dim dataRow As DataRow = table.NewRow()

                    row.GetCell(0).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(1).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(2).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(3).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(4).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(5).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(6).SetCellType(NPOI.SS.UserModel.CellType.STRING)
                    row.GetCell(7).SetCellType(NPOI.SS.UserModel.CellType.STRING)

                    dataRow("NO") = row.GetCell(0).StringCellValue
                    dataRow("CountyID") = row.GetCell(1).StringCellValue
                    dataRow("SerialGroup") = row.GetCell(2).StringCellValue
                    dataRow("ID") = row.GetCell(3).StringCellValue
                    dataRow("Name") = row.GetCell(4).StringCellValue
                    dataRow("SerialNumber") = row.GetCell(5).StringCellValue
                    dataRow("StatePaperID") = row.GetCell(6).StringCellValue
                    dataRow("date") = row.GetCell(7).StringCellValue

                    table.Rows.Add(dataRow)
                Next

                workbook = Nothing
                sheet = Nothing

                Try

                    '存入資料               

                    Dim dat As New clsData
                    Dim sql As New MyStringBuilder

                    Using scope As New Transactions.TransactionScope

                        For Each DR As DataRow In table.Rows

                            sql.Length = 0

                            sql.AppendFormat("SELECT COUNT(*) ")
                            sql.AppendFormat(" from VolunteerDistinctView v ")
                            sql.AppendFormat(" inner join VolunteerStat vs on v.VolunteerID = vs.VolunteerID ")
                            '基礎和特殊達到時數就可授證
                            sql.AppendFormat(" inner join VBookParameter VP on vs.TrainingBHour>=VP.BasicTrainingHour ")
                            sql.AppendFormat(" and vs.TrainingSHour>=VP.SpecialTrainingHour ")
                            sql.AppendFormat(" inner join Village r on v.VillageID = r.RegionID ")
                            sql.AppendFormat(" where v.Active IN ('2','Y') ")
                            sql.AppendFormat(" and  v.VolunteerID='{0}' ", DR("ID").ToString())

                            Dim strtheCount As String = dat.GetSingleData(sql.ToString(), Nothing, Data.CommandType.Text)

                            If (Convert.ToInt32(strtheCount) = 0) Then
                                MyObj.ShowMessage(String.Format("編號[{0}]，不存在於訓練合格儲備志工列表!", DR("NO")))
                                Exit Sub
                            End If

                            If DR("SerialNumber").ToString().Length < 6 OrElse DR("SerialNumber").ToString().Length > 10 Then
                                MyObj.ShowMessage(String.Format("編號[{0}] 紀錄冊號碼長度錯誤!!", DR("NO")))
                                Exit Sub
                            End If

                            If DR("StatePaperID").ToString().Length <> 10 Then
                                MyObj.ShowMessage(String.Format("編號[{0}] 收文號碼長度錯誤!!", DR("NO")))
                                Exit Sub
                            End If

                            Dim theDate As DateTime

                            If (Not DateTime.TryParse(DR("date").ToString(), theDate)) Then
                                MyObj.ShowMessage(String.Format("編號[{0}]  授證日期錯誤!!", DR("NO")))
                                Exit Sub
                            End If


                            '檢查縣市	
                            sql.Length = 0
                            sql.AppendFormat("SELECT COUNT(*) ")
                            sql.AppendFormat(String.Format("from RegionAlias where AliasGroup = 'VBook' and AliasName='{0}' ", DR("CountyID").ToString()))

                            Dim strtheCount2 As String = dat.GetSingleData(sql.ToString(), Nothing, Data.CommandType.Text)

                            If (Convert.ToInt32(strtheCount2) = 0) Then
                                MyObj.ShowMessage(String.Format("編號[{0}]，縣市的值不正確!", DR("NO")))
                                Exit Sub
                            End If

                            '檢查領域
                            sql.Length = 0
                            sql.AppendFormat("SELECT COUNT(*) ")
                            sql.AppendFormat(String.Format("from CodeMapping cm where cm.TableName = 'VBook' and cm.ColumnName = 'SerialGroupID' and Active = 'Y' and [Text]='{0}' ", DR("SerialGroup").ToString()))

                            Dim strtheCount3 As String = dat.GetSingleData(sql.ToString(), Nothing, Data.CommandType.Text)

                            If (Convert.ToInt32(strtheCount3) = 0) Then
                                MyObj.ShowMessage(String.Format("編號[{0}]，領域的值不正確!", DR("NO")))
                                Exit Sub
                            End If

                            sql.Clear()
                            sql.AppendFormat("insert VBook (CountyID,SerialGroupID,SerialNumber,StatePaperID,VolunteerID,IssueDate,CreateDate,CreateBy,Printed,Status,Active) ")
                            sql.AppendFormat("select ")
                            sql.AppendFormat("(select RegionID from RegionAlias where AliasGroup = 'VBook' and AliasName='{0}') ", DR("CountyID").ToString())
                            sql.AppendFormat(",(select [Value] from CodeMapping cm where cm.TableName = 'VBook' and cm.ColumnName = 'SerialGroupID' and Active = 'Y' and [Text]='{0}') ", DR("SerialGroup").ToString())
                            sql.AppendFormat(",'{0}' ", DR("SerialNumber").ToString())
                            sql.AppendFormat(",'{0}' ", DR("StatePaperID").ToString())
                            sql.AppendFormat(", A.VolunteerID ")
                            sql.AppendFormat(",'{0:yyyy/MM/dd}' ", Convert.ToDateTime(DR("date").ToString()))
                            sql.AppendFormat(",{0} ", "getdate()")
                            sql.AppendFormat(",'{0}' ", Page.User.Identity.Name)
                            sql.AppendFormat(",'{0}' ", "N")
                            sql.AppendFormat(",'{0}' ", "2") '初次申請
                            sql.AppendFormat(",'{0}' ", "Y")
                            sql.AppendFormat("FROM VolunteerDistinctView A ")
                            sql.AppendFormat("inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
                            sql.AppendFormat("WHERE A.Active IN ('2','Y') ")
                            sql.AppendFormat("and  A.VolunteerID='{0}' ", DR("ID").ToString())
                            sql.AppendFormat("and  A.VolunteerID not in (SELECT VolunteerId FROM VBook WHERE VolunteerId='{0}' ) ", DR("ID").ToString())

                            dat.AccData(sql)

                            sql.Clear()
                            sql.AppendFormat("update Volunteer set")
                            sql.AppendFormat(" Active='Y' ")
                            sql.AppendFormat(",UpdateDate={0} ", "getdate()")
                            sql.AppendFormat(",UpdateBy='{0}' ", Page.User.Identity.Name)
                            sql.AppendFormat(" where VolunteerID='{0}'; ", DR("ID").ToString())
                            dat.AccData(sql)

                        Next
                        scope.Complete()
                        MyObj.ShowMessage("上傳完成。")
                    End Using
                Catch ex As System.Exception
                    MyObj.ShowMessage(GetLocalResourceObject("UploadNG"))
                    Exit Sub
                End Try

                RaiseEvent Loaded(Me, New EventArgs)

            Catch ex As System.Exception
                MyObj.ShowMessage(GetLocalResourceObject("FileNG"))
                Exit Sub

            End Try
        Else
            MyObj.ShowMessage("請選擇上傳檔案")
        End If

    End Sub

End Class

Partial Class WebForm_SearchVBook

    Public Sub BindGV()
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As Data.DataTable

        sql.AppendFormat(" select v.VolunteerID,v.VolunteerName ")
        sql.AppendFormat(",vs.TrainingBHour, vs.TrainingSHour, vs.TrainingWHour ")
        sql.AppendFormat(",oo.VOrganizationName ")
        sql.AppendFormat(" from VolunteerDistinctView v ")
        sql.AppendFormat(" inner join VolunteerStat vs on v.VolunteerID = vs.VolunteerID ")

        '基礎和特殊達到時數就可授證
        sql.AppendFormat(" inner join VBookParameter VP on vs.TrainingBHour>=VP.BasicTrainingHour ")
        sql.AppendFormat(" and vs.TrainingSHour>=VP.SpecialTrainingHour ")
        'sql.AppendFormat(" and vs.TrainingWHour>=VP.WorkTrainingHour ")

        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on v.VolunteerID=oo.VolunteerID  ")

        sql.AppendFormat(" where v.Active  in ('2') ")
        sql.AppendFormat(GetWhereSQL())

        sql.AppendFormat(Me.gv.OrderBySQL)
        ' MyObj.WriteErrorLog(sql.ToString())
        dt = dat.GetData(sql)

        Me.gv.Columns(6).Visible = Me.IsVisible
        Me.gv.DataSource = dt
        Me.gv.DataBind()
    End Sub


    Protected Function BindGV2() As Data.DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder


        sql.AppendFormat(" select v.PhotoPath,v.PureVolunteerName,v.VolunteerID,v.Address,v.OfficePhone ")
        sql.AppendFormat(",Case v.Gender when 'F' then '女' else '男' end vGender ")
        sql.AppendFormat(", vBirthday = v.Birthday ")
        sql.AppendFormat(",R1.RegionName")
        sql.AppendFormat(",2 as VBookTypeID, '050' as SerialGroupID")
        sql.AppendFormat(",'' as Picture,'V' as GetVBook,'' as ChangeVBook ")
        sql.AppendFormat(", tgbStartDate = tgb.StartDate ")
        sql.AppendFormat(", o_tgb.OrganizationName as trbOrg, o_tgs.OrganizationName as trsOrg ")
        sql.AppendFormat(", tgsStartDate = tgs.StartDate ")
        sql.AppendFormat(", VBookID = null ")
        sql.AppendFormat(", r1.RegionID ")
        sql.AppendFormat(",oo.VOrganizationName ")
        sql.AppendFormat(",org.OrganizationName ")
        sql.AppendFormat(" from VolunteerDistinctView v ")
        'sql.AppendFormat(" inner join VBookView vb on v.VolunteerID = vb.VolunteerID ")
        sql.AppendFormat(" inner join VolunteerStat vs on v.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat(" inner join VBookParameter VP on vs.TrainingBHour>=VP.BasicTrainingHour ")
        sql.AppendFormat(" and vs.TrainingSHour>=VP.SpecialTrainingHour ")
        'sql.AppendFormat(" and vs.TrainingWHour>=VP.WorkTrainingHour ")
        sql.AppendFormat(" Left join Village R1 on v.VillageID = R1.RegionID ")

        '最近一筆基礎訓上課紀錄
        sql.AppendFormat(" left join ( select VolunteerID, GroupID = max(GroupID) from TrainingRegister trb ")
        sql.AppendFormat("          inner join trainingcoursegroup tgb on trb.GroupID = tgb.ID and tgb.GroupType='B' where trb.CheckStatus = '3' group by VolunteerID ")
        sql.AppendFormat("  ) tgb2 on v.VolunteerID = tgb2.VolunteerID ")

        sql.AppendFormat(" left join TrainingCourseGroup tgb on tgb.ID = tgb2.GroupID ")

        '最近一筆特殊訓上課紀錄
        sql.AppendFormat(" left join ( select VolunteerID, GroupID = max(GroupID) from TrainingRegister trs ")
        sql.AppendFormat("          inner join trainingcoursegroup tgs on trs.GroupID = tgs.ID and tgs.GroupType='S' where trs.CheckStatus = '3' group by VolunteerID ")
        sql.AppendFormat("  ) tgs2 on v.VolunteerID = tgs2.VolunteerID ")

        sql.AppendFormat(" left join TrainingCourseGroup tgs on tgs.ID = tgs2.GroupID ")

        'sql.AppendFormat(" left join (select tgs.StartDate, trs.VolunteerID from trainingregister trs ")
        'sql.AppendFormat(" inner join trainingcoursegroup tgs on trs.GroupID = tgs.ID and tgs.GroupType='S' ")
        'sql.AppendFormat(" where trs.CheckStatus = '3') tgs on v.VolunteerID = tgs.VolunteerID ")
        sql.AppendFormat(" left join OrganizationView o_tgb on tgb.UOrganizationID = o_tgb.UOrganizationID ")
        sql.AppendFormat(" left join OrganizationView o_tgs on tgs.UOrganizationID = o_tgs.UOrganizationID ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on v.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat("left join  OrganizationView org on oo.DirectUOrganizationID=org.UOrganizationID ")
        sql.AppendFormat(" where 1=1 ")
        sql.AppendFormat("and v.Active in ('2')  ")
       
        sql.AppendFormat(GetWhereSQL())

        sql.AppendFormat(" order by org.OrganizationName,oo.VOrganizationName ")
        ' MyObj.WriteErrorLog(sql.ToString())

        Return dat.GetData(sql)

    End Function

    Private Sub Export()
        Dim vcList As New List(Of VBook)
        Dim v As VBook
        Dim dt As System.Data.DataTable

        dt = Me.BindGV2()

        Dim base As Integer = 4

        Dim i As Integer = 0
        Dim prevRegionID As String
        Dim RowCount As Integer = 1


        'Dim CmpVolunteerID As String = ""
        For Each dr As Data.DataRow In dt.Rows
            If prevRegionID <> "" AndAlso prevRegionID <> dr("VOrganizationName") Then

                If i Mod base <> 0 Then
                    For j As Integer = i Mod base To base - 1
                        v = New VBook
                        vcList.Add(v)

                        i = i + 1
                    Next
                End If
                RowCount = (base - (RowCount Mod 4)) + 1 + RowCount
                i = 0
            End If


       
            'If Not CmpVolunteerID.Contains(dr("VolunteerID").ToString) Then
            v = New VBook
            If Not TypeOf dr("PhotoPath") Is DBNull Then
                v.Picture = Me.GetPhoto2(dr("PhotoPath").ToString())
            Else
                v.Picture = ""
            End If

            If Not TypeOf dr("OrganizationName") Is DBNull AndAlso RowCount Mod base = 1 Then
                v.OrganizationName = dr("OrganizationName").ToString() & Space(1) & dr("VOrganizationName").ToString()
            Else
                v.OrganizationName = ""
            End If

            If v.OrganizationName.Length > 9 Then
                v.VSpace = Space(51 - (v.OrganizationName.Length - 10) * 2)
            Else
                v.OrganizationName = Space(16)
                v.VSpace = Space(51)
            End If

            v.RegionName = dr("RegionName").ToString
            v.VolunteerName = dr("PureVolunteerName").ToString
            v.vGender = dr("vGender").ToString
            v.VolunteerID = dr("VolunteerID").ToString
            'CmpVolunteerID &= dr("VolunteerID").ToString & ","
            v.vBirthday = dr("vBirthday").ToString()
            v.tgbStartDate = dr("tgbStartDate").ToString

            v.trbOrg = dr("trbOrg").ToString
            v.tgsStartDate = dr("tgsStartDate").ToString
            v.trsOrg = dr("trsOrg").ToString
            v.Address = dr("Address").ToString
            v.OfficePhone = dr("OfficePhone").ToString
            v.GetVBook = dr("GetVBook").ToString
            v.ChangeVBook = dr("ChangeVBook").ToString

            v.Index = RowCount + 2
            vcList.Add(v)

            i = i + 1
            RowCount = RowCount + 1

            If Not TypeOf (dr("VOrganizationName")) Is DBNull Then
                prevRegionID = dr("VOrganizationName")
            Else
                prevRegionID = ""
            End If

            '  End If





        Next
        'Print

        Dim fileName As String = "VBook.doc"
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName)
        Response.ContentType = "application/vnd.ms-excel"

        Dim velocity As New VelocityEngine
        Dim props As New Commons.Collections.ExtendedProperties

        'fix bug
        props.AddProperty("file.resource.loader.path", Server.MapPath("~\App_Data"))
        props.AddProperty("input.encoding", "utf-8")
        props.AddProperty("output.encoding", "utf-8")
        velocity.Init(props)

        Dim template As Template

        Dim context As New VelocityContext
        template = velocity.GetTemplate("臺北縣環境保護志願服務紀錄冊核發名冊 v01.xml")
        context.Put("User", clsAuth.GetUserID())
        context.Put("DownloadDate", Today.Year.ToString() & "-" & Today.Month.ToString() & "-" & Today.Day.ToString() & "T" & Now.Hour.ToString() & ":" & Now.Minute.ToString() & ":" & Now.Second.ToString() & "Z")
        context.Put("IP", MyObj.getIP())

        'context.Put("OrganizationName", Me.VolunteerQueryControl1.VOrganizationName)

        context.Put("Title", "新北市環境保護志願服務紀錄冊請領清冊")

        Dim approveDate As Date = Date.Now
        context.Put("ApproveDate", approveDate.Date.ToString("yyyy年MM月dd日"))
        context.Put("ApproveDateLabel", "申請日期:")
        context.Put("VBook", vcList)

        Dim writer As New IO.StringWriter
        template.Merge(context, writer)

        Response.Write(writer.GetStringBuilder().ToString())

        Response.End()



 
    End Sub

    Private ReadOnly Property photoBasePath() As String
        Get
            If ViewState("photoBasePath") Is Nothing Then
                ViewState("photoBasePath") = MyObj.GetFilePath("VBookPhoto")
            End If

            Return ViewState("photoBasePath")
        End Get
    End Property
    Private Function GetPhoto2(ByVal photoFileName As String) As String
        Dim photoPath As String = Server.MapPath(Me.photoBasePath + photoFileName)
        Dim ReturnValue As String = ""
        Try
            If File.Exists(photoPath) Then
                Dim data As String = Nothing
                Dim img As System.Drawing.Image
                img = System.Drawing.Image.FromFile(photoPath)

                Try
                    Dim MyRandom As New clsRandom
                    Dim MyPath As String = Server.MapPath(Me.photoBasePath + MyRandom.Generate(8) & ".jpg")
                    img.Save(MyPath, System.Drawing.Imaging.ImageFormat.Jpeg)
                    img.Dispose()

                    Using s = New BinaryReader(New FileStream(MyPath, FileMode.Open))
                        Dim b() As Byte = s.ReadBytes(s.BaseStream.Length)

                        Dim ss As String = Convert.ToBase64String(b)


                        ReturnValue = ss
                    End Using

                    File.Delete(MyPath)
                    Return ReturnValue
                Catch ex As System.Exception
                    MyObj.WriteErrorLog(ex.Message)
                    Return ""
                End Try
            Else
                Return ""
            End If
        Catch ex As System.Exception
            MyObj.WriteErrorLog(ex.Message)
            Return ""
        End Try
    End Function
    Private Function GetPhoto(ByVal photoFileName As String) As String
        'Dim photoPath As String = Me.photoBasePath + "\" + Integer.Parse(vbookID).ToString() + ".png"
        Dim photoPath As String = Server.MapPath(Me.photoBasePath + "\" + photoFileName)

        'Dim photo As System.Drawing.Image = System.Drawing.Image.FromFile("a")
        'MyObj.WriteLog(photoPath)
        Try
            Using s As New BinaryReader(New FileStream(photoPath, FileMode.Open))
                Dim b() As Byte = s.ReadBytes(s.BaseStream.Length)

                Return Convert.ToBase64String(b)
            End Using
        Catch ex As System.Exception
            MyObj.WriteLog(ex.Message)
            Return ""
        End Try
    End Function
    Public Class VBook
        Private OrganizationNameField As String = ""
        Private SerialNumberField As String = ""
        Private VBookIDField As String = ""
        Private PictureField As String = ""
        Private RegionNameField As String = ""
        Private VolunteerNameField As String = ""
        Private vGenderField As String = ""
        Private VolunteerIDField As String = ""
        Private vBirthdayField As String = ""
        Private tgbStartDateField As String = ""
        Private trbOrgField As String = ""
        Private tgsStartDateField As String = ""
        Private trsOrgField As String = ""
        Private AddressField As String = ""
        Private OfficePhoneField As String = ""
        Private GetVBookField As String = ""
        Private ChangeVBookField As String = ""
        Private IndexField As Integer
        Private VSpaceField As String = ""

        Public Property VSpace() As String
            Get
                Return Me.VSpaceField
            End Get
            Set(ByVal value As String)
                Me.VSpaceField = value
            End Set
        End Property
        Public Property OrganizationName() As String
            Get
                Return Me.OrganizationNameField
            End Get
            Set(ByVal value As String)
                Me.OrganizationNameField = value
            End Set
        End Property

        Public Property Index() As Integer
            Get
                Return Me.IndexField
            End Get
            Set(ByVal value As Integer)
                Me.IndexField = value
            End Set
        End Property

        Public Property SerialNumber() As String
            Get
                Return Me.SerialNumberField
            End Get
            Set(ByVal value As String)
                Me.SerialNumberField = value
            End Set
        End Property

        Public Property VBookID() As String
            Get
                Return Me.VBookIDField
            End Get
            Set(ByVal value As String)
                Me.VBookIDField = value
            End Set
        End Property
        Public Property Picture() As String
            Get
                Return Me.PictureField
            End Get
            Set(ByVal value As String)
                Me.PictureField = value
            End Set
        End Property
        Public Property RegionName() As String
            Get
                Return Me.RegionNameField
            End Get
            Set(ByVal value As String)
                Me.RegionNameField = value
            End Set
        End Property
        Public Property VolunteerName() As String
            Get
                Return Me.VolunteerNameField
            End Get
            Set(ByVal value As String)
                Me.VolunteerNameField = value
            End Set
        End Property
        Public Property vGender() As String
            Get
                Return Me.vGenderField
            End Get
            Set(ByVal value As String)
                Me.vGenderField = value
            End Set
        End Property
        Public Property VolunteerID() As String
            Get
                Return Me.VolunteerIDField
            End Get
            Set(ByVal value As String)
                Me.VolunteerIDField = value
            End Set

        End Property
        Public Property vBirthday() As String
            Get
                Return Me.vBirthdayField
            End Get
            Set(ByVal value As String)
                Me.vBirthdayField = value
            End Set
        End Property
        Public Property tgbStartDate() As String
            Get
                Return Me.tgbStartDateField
            End Get
            Set(ByVal value As String)
                Me.tgbStartDateField = value
            End Set
        End Property
        Public Property trbOrg() As String
            Get
                Return Me.trbOrgField
            End Get
            Set(ByVal value As String)
                Me.trbOrgField = value
            End Set
        End Property
        Public Property tgsStartDate() As String
            Get
                Return Me.tgsStartDateField
            End Get
            Set(ByVal value As String)
                Me.tgsStartDateField = value
            End Set
        End Property
        Public Property trsOrg() As String
            Get
                Return Me.trsOrgField
            End Get
            Set(ByVal value As String)
                Me.trsOrgField = value
            End Set

        End Property
        Public Property Address() As String
            Get
                Return Me.AddressField
            End Get
            Set(ByVal value As String)
                Me.AddressField = value
            End Set
        End Property
        Public Property OfficePhone() As String
            Get
                Return Me.OfficePhoneField
            End Get
            Set(ByVal value As String)
                Me.OfficePhoneField = value
            End Set
        End Property
        Public Property GetVBook() As String
            Get
                Return Me.GetVBookField
            End Get
            Set(ByVal value As String)
                Me.GetVBookField = value
            End Set
        End Property
        Public Property ChangeVBook() As String
            Get
                Return Me.ChangeVBookField
            End Get
            Set(ByVal value As String)
                Me.ChangeVBookField = value
            End Set
        End Property

    End Class

    Private Function GetWhereSQL() As String
        Dim sql As New MyStringBuilder

        If Me.VolunteerQueryControl1.VolunteerId.Trim() = "" Then
            sql.AppendFormat(" and (v.VolunteerName like '%{0}%' or ''='{0}')", Me.VolunteerQueryControl1.VolunteerName.Trim())
            sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", Me.VolunteerQueryControl1.VOrganizationID)
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}' or '{0}'='0' ) ", Me.VolunteerQueryControl1.OrganizationID)

        Else
            sql.AppendFormat(" and (v.VolunteerID='{0}' or ''='{0}')", Me.VolunteerQueryControl1.VolunteerId.Trim())
        End If

        Return sql.ToString()
    End Function

    Private Function GetDate(ByVal oldName As String, ByVal newName As String) As String
        Dim sql As New MyStringBuilder

        sql.AppendFormat(",Case " + oldName + " when null then null ")
        sql.AppendFormat(" else Cast((Year(" + oldName + ")-1911) as varchar)+'.' ")
        sql.AppendFormat(" +Right('0'+Cast(month(" + oldName + ") as varchar),2)+'.' ")
        sql.AppendFormat(" +Right('0'+Cast(day(" + oldName + ") as varchar),2) end " + newName + " ")

        Return sql.ToString()
    End Function

    'Private ReadOnly Property IsOrganization() As Boolean
    '    Get
    '        If Me.ddlOrganizationID.SelectedValue.Split(",")(0) = "-1" Then
    '            Return False
    '        Else
    '            Return True
    '        End If
    '    End Get
    'End Property

    'Private ReadOnly Property OrganizationRoot() As String
    '    Get
    '        Return Me.ddlOrganizationID.SelectedValue.Split(",")(0)
    '    End Get
    'End Property

    'Private ReadOnly Property VOrganizationRoot() As String
    '    Get
    '        Return Me.ddlOrganizationID.SelectedValue.Split(",")(1)
    '    End Get
    'End Property

    Public ReadOnly Property SelectedVolunteerID() As String
        Get
            If Me.gv.SelectedRow Is Nothing Then
                Return Me.gv.DataKeys(0)("VolunteerID")
            Else
                Return Me.gv.DataKeys(Me.gv.SelectedRow.RowIndex)("VolunteerID")
            End If
        End Get
    End Property
    Public Property IsVisible() As Boolean
        Get
            If ViewState("IsVisible") Is Nothing Then
                ViewState("IsVisible") = True
            End If
            Return ViewState("IsVisible")
        End Get
        Set(ByVal value As Boolean)
            ViewState("IsVisible") = value
        End Set
    End Property
    Public Event Loaded(ByVal source As Object, ByVal e As EventArgs)
    Public Event SelectedIndexChanged(ByVal source As Object, ByVal e As EventArgs)
End Class